﻿using System;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using FileHelpers;
using System.Configuration;
using Microsoft.SharePoint;
using Microsoft.SharePoint.Administration.Health;

namespace Docsbox.DataMigration
{
    class Program
    {
        public static string FinanceBranchCode = "FinanceBranchCode";
        static void Main(string[] args)
        {
            UpdateAccountsIntoOfficesFolder();
            //DataMigration();
            //UpdateContractDateAndRevisionDate();
            //SetConsultantForBranch();
            Console.WriteLine("Finished.");
            Console.ReadLine();
        }

        private static void SetConsultantForBranch()
        {
                       
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {
                    SPUser Whangarei_susuan = docsbox.EnsureUser("Auctions\\strombik");
                    SPUser northshore_scott = docsbox.EnsureUser("Auctions\\syu");
                    SPUser Tauranga_Daniel = docsbox.EnsureUser("Auctions\\dpeet");
                    SPUser hamilton_robyn = docsbox.EnsureUser("Auctions\\rsutherland");
                    SPUser napier_Paul = docsbox.EnsureUser("Auctions\\pmckenzie");
                    SPUser palmerstonNorth_bill = docsbox.EnsureUser("Auctions\\bwalter");
                    SPUser wellington_sally = docsbox.EnsureUser("Auctions\\sdennehy");
                    SPUser christchurch_les = docsbox.EnsureUser("Auctions\\leslaming");
                    SPUser dunedin_les = docsbox.EnsureUser("Auctions\\leslaming");


                    Console.WriteLine("users are all loaded.");
                    Console.ReadLine();
                    Console.WriteLine("start processing users.");
                    SPList accounts = docsbox.Lists[Common.ListAccounts];
                    int count = 0;
                    foreach (SPListItem item in accounts.Items)
                    {
                        string office = item[Common.FieldFinanceOffice].ToString();
                        if(office.ToUpper().Contains("WHANGAREI"))
                        {
                            item[Common.FieldAccountOwner] = Whangarei_susuan;
                            item.Update();
                            count++;
                        }
                        if(office.ToUpper().Contains("NORTH SHORE"))
                        {
                            item[Common.FieldAccountOwner] = northshore_scott;
                            item.Update();
                            count++;
                        }
                        if(office.ToUpper().Contains("TAURANGA"))
                        {
                            item[Common.FieldAccountOwner] = Tauranga_Daniel;
                            item.Update();
                            count++;
                        }
                        if(office.ToUpper().Contains("HAMILTON"))
                        {
                            item[Common.FieldAccountOwner] = hamilton_robyn;
                            item.Update();
                            count++;
                        }
                        if(office.ToUpper().Contains("NAPIER"))
                        {
                            item[Common.FieldAccountOwner] = napier_Paul;
                            item.Update();
                            count++;
                        }
                        if (office.ToUpper().Contains("PALMERSTON NORTH"))
                        {
                            item[Common.FieldAccountOwner] = palmerstonNorth_bill;
                            item.Update();
                            count++;
                        }
                        if(office.ToUpper().Contains("CHRISTCHURCH"))
                        {
                            item[Common.FieldAccountOwner] = christchurch_les;
                            item.Update();
                            count++;
                        }
                        if (office.ToUpper().Contains("DUNEDIN"))
                        {
                            item[Common.FieldAccountOwner] = dunedin_les;
                            item.Update();
                            count++;
                        }
                        if(office.ToUpper().Contains("WELLINGTON"))
                        {
                            item[Common.FieldAccountOwner] = wellington_sally;
                            item.Update();
                            count++;
                        }
                    }

                }
            }
        }

        private static void UpdateContractDateAndRevisionDate()
        {
            string filename = ConfigurationManager.AppSettings["updatedatesfilePath"];

            FileHelperEngine engine = new FileHelperEngine(typeof(DocsboxDates));

            DocsboxDates[] records = engine.ReadFile(filename) as DocsboxDates[];

            int count = 0;

            List<DocsboxUpdater> updater = new List<DocsboxUpdater>();
            foreach (DocsboxDates record in records)
            {
                if(record.ValidateMtfNo())
                {
                    count++;
                    record.project = record.project.Substring(3);
                    DateTime reviewDate = DateTime.Now;
                    if(record.review_date.Length > 0)
                    {
                        bool reviewdateSuccess = DateTime.TryParseExact(record.review_date, "d/MM/yyyy H:mm", null,
                                                                        DateTimeStyles.AllowWhiteSpaces, out reviewDate);

                    }
                    DateTime contractDate = DateTime.Now;
                    if(record.created.Length > 0)
                    {
                        bool contractdateSuccess = DateTime.TryParseExact(record.created, "d/MM/yyyy H:mm", null,
                                                              DateTimeStyles.None, out contractDate);
                    }
                    updater.Add(new DocsboxUpdater
                                    {
                                        DealId = record.project,
                                        RevisionDate = reviewDate == DateTime.MinValue ? DateTime.Now : reviewDate,
                                        ContractDate = contractDate == DateTime.MinValue ? DateTime.Now : contractDate
                                    });

                    //Console.WriteLine(record.project + ", " + reviewDate.ToString("dd/MM/yyyy") + ", " + contractDate.ToString("dd/MM/yyyy H:mm"));
                }

                if(count%100 == 0)
                {
                    Console.WriteLine("Count: " + count);
                }
            }
            Console.ReadLine();
            Console.WriteLine("Update the sharepoint list now ...");
            UpateAccountDates(updater);


            Console.WriteLine("Completed!");
            Console.ReadLine();
        }

        private static void DataMigration()
        {
            Console.WriteLine("start");
            //migrate data from file system and csv to sharepoint
            //MigrateDocsboxData();
            //Console.WriteLine("data migration completed");
            //create office folders in accounts list, and move the items to office folders which office name is same as the folder name

            //BEFORE RUN THIS SCRIPT, ADD FOLDERS OF THE BRANCHES TO THE ACCOUNT LIST !!!IMPORTANT!!!
            //UpdateAccountsIntoOfficesFolder();
            //Console.WriteLine("accounts are moved to branch folder");
            //move PDF documents into folder of the last two digit
            //UpdateDocumentsIntoFolderAccordingItsAccountNameLastTwoDigit();
            //Console.WriteLine("PDF documetns are moved to folder of last two digit of deal no.");
            //remove MTF prefix from Account Deal Number
            //RemoveMTFPrefix();
            SetPDFDocumentsIsNewFieldToFalse();
            Console.WriteLine("IsNew field are set to false.");
            //remove all data from sharepoint account list and pdf documents from document library
            //CleanData();
            Console.WriteLine("Completed!");
            Console.ReadLine();
        }

        private static void SetPDFDocumentsIsNewFieldToFalse()
        {
            Console.WriteLine("Start Processing Is New Field");
            int counter = 0;
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {

                    SPList documents = docsbox.Lists[Common.ListDocuments];

                    int processIndex = 0;

                    foreach (SPListItem item in documents.Items)
                    {
                        counter++;
                        item["IsNew"] = false;
                        item.Update();
                        if (counter % 100 == 0)
                            Console.WriteLine("Processed Record: " + counter);
                    }
                }
            }
        }

        private static void RemoveMTFPrefix()
        {
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {

                    SPList documents = docsbox.Lists[Common.ListAccounts];
                    foreach (SPListItem item in documents.Items)
                    {
                        string title = item["Title"].ToString();
                        if(title.StartsWith("MTF"))
                        {
                            title = title.Substring(3);
                            item["Title"] = title;
                            item.Update();                            
                        }
                    }
                }
            }
        }

        private static void UpdateDocumentsIntoFolderAccordingItsAccountNameLastTwoDigit()
        {
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {

                    SPList documents = docsbox.Lists[Common.ListDocuments];

                    int processIndex = 0;

                    foreach (SPListItem item in documents.Items)
                    {
                        if(processIndex %100 == 0)
                            Console.WriteLine("Processed : " + processIndex);
                        processIndex++;
                        string mtfNo = Common.ConvertFieldToString(item[Common.FieldAccountLookup], FieldType.Lookup);

                        if(!string.IsNullOrEmpty(mtfNo))
                        {
                            if(item.Url.IndexOf('/') == item.Url.LastIndexOf('/'))
                            {
                                bool folderCreated = false;
                                string folderName = mtfNo.Substring(mtfNo.Length - 2);

                                SPListItemCollection folders = documents.Folders;
                                foreach (SPListItem spListItem in folders)
                                {
                                    if(spListItem.Name.Equals(folderName))
                                    {
                                        folderCreated = true;
                                        break;
                                    }
                                }

                                if(!folderCreated)
                                {
 
                                    SPListItem folder = documents.Items.Add(documents.RootFolder.ServerRelativeUrl,
                                                                          SPFileSystemObjectType.Folder, null);
                                    if(folder != null)
                                    {
                                        folder["Name"] = folderName;
                                        folder.Update();
                                    }                              
                                }

                                SPFile file = documents.ParentWeb.GetFile(item.Url);

                                string folderPattern = folderName + "/";
                                if (processIndex % 100 == 0)
                                    Console.WriteLine("Item Url: " + item.Url +", folder pattern: " + folderPattern);

                                if(!item.Url.Contains(folderPattern))
                                {

                                     string destinationUrl = string.Format("{0}/{1}/{2}",
                                                                            item.Url.Substring(0, item.Url.IndexOf('/')), folderName, item.Name);
                                    file.MoveTo(destinationUrl, true);                                      
                                }
                            }
                            
                        }

                    }
                }
            }
        }

        private static void UpdateAccountsIntoOfficesFolder()
        {
            List<string> folders = new List<string>();
            int counter = 0;
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {

                    SPList offices = docsbox.Lists[Common.ListOffices];

                    foreach (SPListItem item in offices.Items)
                    {
                        folders.Add(Common.ConvertFieldToString(item["Title"], FieldType.Text));
                        Console.WriteLine("Office " + Common.ConvertFieldToString(item["Title"], FieldType.Text) + " is added.");
                    }


                    SPList accounts = docsbox.Lists[Common.ListAccounts];
                    Console.WriteLine("List Accounts is loaded.");

                    foreach (SPListItem item in accounts.Items)
                    {
                        bool alreadyProcessed = false;
                        //foreach (string folder in folders)
                        //{
                        //    if (item.Url.Contains(folder))
                        //    {
                        //        alreadyProcessed = true;
                        //        break;
                        //    }
                        //}

                        //if(!alreadyProcessed)
                        //{
                             SPFile file = accounts.ParentWeb.GetFile(item.Url);

                            string office = Common.ConvertFieldToString(item["FinanceOffice"], FieldType.Lookup);
                            string destinationUrl = file.Url.Replace(item.ID.ToString() + "_.000",
                                                                          office + "/" + item.ID.ToString() + "_.000");
                            //file.MoveTo(destinationUrl,true);
                            counter++;                           
                        //}
                    }
                }
            }
            Console.WriteLine("Total record processed: " + counter);
            Console.ReadLine();
        }

        private static void CleanData()
        {

            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {
                    SPList accountDocs = docsbox.Lists[Common.ListDocuments];

                    for(int i= accountDocs.Items.Count -1; i >=0; i--)
                    {
                        SPListItem item = accountDocs.Items[i];
                        item.File.Delete();
                        item.Delete();
                    }

                    SPList accounts = docsbox.Lists[Common.ListAccounts];

                    for (int i = accounts.Items.Count - 1; i >= 0; i--)
                    {
                        SPListItem item = accounts.Items[i];
                        item.Delete();
                    }
                }
            }
        }

        private static void MigrateDocsboxData()
        {
            string filename = ConfigurationManager.AppSettings["filePath"];

            FileHelperEngine engine = new FileHelperEngine(typeof (Docsbox));

            Docsbox[] records = engine.ReadFile(filename) as Docsbox[];

            int documentProcessedCount = 0;
            int incorrectDocumentCount = 0;
            string target = CreateFolder();
            using (CsvFileWriter writer = new CsvFileWriter("error-log.csv"))
            {
                CsvRow header = new CsvRow();
                header.AddRange(Docsbox.CsvHeaders());
                writer.WriteRow(header);
                foreach (Docsbox record in records)
                {
                    if (record.IsValidRecord())
                    {
                        record.MTFNo = record.MTFNo.Substring(3);
                        int accountId = UpdateAccount(record);
                        UploadDocument(record, accountId);
                        documentProcessedCount++;
                        if(documentProcessedCount%100 == 0)
                        {
                            Console.WriteLine(string.Format("{0} documents processed.",documentProcessedCount));
                        }
                    }
                    else
                    {
                        CsvRow row = new CsvRow();
                        MoveFile(record,target);
                        row.AddRange(record.ToCsvErrorList());
                        writer.WriteRow(row);
                        incorrectDocumentCount++;
                    }
                }
            }
            Console.WriteLine(string.Format("{0} document processed successful, {1} document failed. Docsbox migration completed!", documentProcessedCount, incorrectDocumentCount));
            Console.ReadLine();
        }

        private static string CreateFolder()
        {
            //Copy failed record PDF document to a separate folder
            string target = string.Format("{0}\\{1}", Directory.GetCurrentDirectory(), "FailedDocuments");
            if (!Directory.Exists(target))
            {
                Directory.CreateDirectory(target);
            }
            else
            {
                DirectoryInfo info = new DirectoryInfo(target);
                foreach (FileInfo fileInfo in info.GetFiles())
                {
                    fileInfo.Delete();
                }
            }
            return target;
        }

        private static void MoveFile(Docsbox record, string targetFolder)
        {
            //Copy failed record PDF document to a separate folder
            if (record.Error() == ErrorCode.MissingPdf || record.Error() == ErrorCode.NoPdfPath)
                return;
            string source = record.FilePath;
            if (!File.Exists(source))
                return;
            string fileName = source.Substring(source.LastIndexOf('\\') + 1);
            string destination = string.Format("{0}\\{1}", targetFolder, fileName);
            File.Copy(source,destination,true);
        }

        private static void UploadDocument(Docsbox record, int accountId)
        {
            if (!File.Exists(record.FilePath))
                return;

            string fileName = record.FilePath.Substring(record.FilePath.LastIndexOf('\\') + 1);
            if (string.IsNullOrEmpty(record.meta_fileName))
                return;

            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {
                    SPList accountDocs = docsbox.Lists[Common.ListDocuments];

                    byte[] fileContent = File.ReadAllBytes(record.FilePath);
                    string url = string.Format("{0}/{1}/{2}/{3}", site.Url.EndsWith("/") ? site.Url.Trim('/') : site.Url,
                                               ConfigurationManager.AppSettings["docsboxWeb"],
                                               Common.ListDocuments,
                                               fileName);
                    SPFile file = accountDocs.RootFolder.Files.Add(url, fileContent,true);
                    SPListItem item = file.Item;
                    item["Title"] = string.IsNullOrEmpty(record.meta_fileName.Trim()) ? fileName : record.meta_fileName.Trim();
                    item[Common.FieldAccountLookup] = accountId;
                    accountDocs.Update();
                    item.Update();
                }
            }

        }

        private static string GetSingleLineofText(string input)
        {
            string output = HttpUtility.UrlDecode(input);
            if (output.Length > 255)
                output = output.Substring(0, 250) + "...";
            return output;
        }

        private static void UpateAccountDates(List<DocsboxUpdater> updateData)
        {
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {
                    SPList accounts = docsbox.Lists[Common.ListAccounts];

                    foreach (SPListItem item in accounts.Items)
                    {
                        string dealNumber = item["Title"].ToString();
                        DocsboxUpdater updateItem = SearchByDealNumber(updateData, dealNumber);
                        if(updateItem != null)
                        {
                            item[Common.FieldRevisionDate] = updateItem.RevisionDate;
                            item[Common.FieldContractDate] = updateItem.ContractDate;
                            item.Update();
                            Console.WriteLine("Account is updated - " + dealNumber);
                        }

                    }

                    //int counter = 0;
                    //foreach (DocsboxUpdater docsboxUpdater in updateData)
                    //{
                    //    counter++;
                    //    int accountId = FindAccount(accounts, docsboxUpdater.DealId);
                    //    if (accountId != -1)
                    //    {
                    //        SPListItem item = accounts.Items[accountId];
                    //        item[Common.FieldRevisionDate] = docsboxUpdater.RevisionDate;
                    //        item[Common.FieldContractDate] = docsboxUpdater.ContractDate;
                    //        item.Update();
                    //    }
                    //    if(counter % 100 == 0)
                    //    {
                    //        Console.WriteLine(counter + " number of records are updated.");
                    //    }
                    //}
                }
            }
        }

        private static DocsboxUpdater SearchByDealNumber(List<DocsboxUpdater> updateData, string dealNumber)
        {
            foreach (DocsboxUpdater updater in updateData)
            {
                if (updater.DealId.Equals(dealNumber))
                    return updater;
            }
            return null;
        }

        private static int UpdateAccount(Docsbox record)
        {
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {
                    SPList accounts = docsbox.Lists[Common.ListAccounts];

                    int accountId = FindAccount(accounts, record.MTFNo);
                    if (accountId != -1)
                        return accountId;

                    SPItem item = accounts.Items.Add();
                    item[Common.FieldMTFNo] = record.MTFNo;
                    item[Common.FieldFirstName] = HttpUtility.UrlDecode(record.FirstName);
                    item[Common.FieldSurname] = HttpUtility.UrlDecode(record.Surname);
                    item[Common.FieldRegNo] = record.RegNo;
                    item[Common.FieldFinanceOffice] = GetOfficeIdByCode(record.officeID);
                    item[Common.FieldDriverLicenceApproved] = ConvertBoolean(record.field1Approve);
                    item[Common.FieldDriverLicenseComment] = GetSingleLineofText(record.field1Comment);

                    item[Common.FieldBankStatementApproved] = ConvertBoolean(record.field2Approve);
                    item[Common.FieldBankStatementComment] = GetSingleLineofText(record.field2Comment);

                    item[Common.FieldConfirmationOfAddressApproved] = ConvertBoolean(record.field3Approve);
                    item[Common.FieldConfirmationOfAddressComment] = GetSingleLineofText(record.field3Comment);

                    item[Common.FieldWagesSlipApproved] = ConvertBoolean(record.field4Approve);
                    item[Common.FieldWagesSlipComment] = GetSingleLineofText(record.field4Comment);

                    item[Common.FieldVisaResidencyApproved] = ConvertBoolean(record.field5Approve);
                    item[Common.FieldVisaResidencyComment] = GetSingleLineofText(record.field5Comment);

                    item[Common.FieldCreditCheckApproved] = ConvertBoolean(record.field6Approve);
                    item[Common.FieldCreditCheckComment] = GetSingleLineofText(record.field6Comment);

                    item[Common.FieldInsuranceApproved] = ConvertBoolean(record.field7Approve);
                    item[Common.FieldInsuranceComment] = GetSingleLineofText(record.field7Comment);

                    item[Common.FieldRPWApproved] = ConvertBoolean(record.field8Approve);
                    item[Common.FieldRPWComment] = GetSingleLineofText(record.field8Comment);

                    item[Common.FieldCDPCoverApproved] = ConvertBoolean(record.field9Approve);
                    item[Common.FieldCDPCoverComment] = GetSingleLineofText(record.field9Comment);

                    item[Common.FieldAffordabilityApproved] = ConvertBoolean(record.field10Approve);
                    item[Common.FieldAffordabilityComment] = GetSingleLineofText(record.field10Comment);

                    item[Common.FieldDepositRequirementApproved] = ConvertBoolean(record.field11Approve);
                    item[Common.FieldDepositRequirementComment] = GetSingleLineofText(record.field11Comment);

                    item[Common.FieldAccountComment] = HttpUtility.UrlDecode(record.Comments);

                    item[Common.FieldAuditComment] = HttpUtility.UrlDecode(record.GeneralComments);

                    item[Common.FieldAccountStatus] = GetStatusIdString(record.Status);


                    item.Update();
                    return item.ID;
                }
            }
        }

        private static int FindAccount(SPList accounts, string mtfNo)
        {
            foreach (SPListItem spListItem in accounts.Items)
            {
                if(spListItem["Title"].ToString().Equals(mtfNo, StringComparison.OrdinalIgnoreCase))
                {
                    return spListItem.ID;
                }
            }
            return -1;
        }

        private static string GetStatusIdString(string status)
        {
            string accountStatus = GetStatus(status);
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {
                    SPList list = docsbox.Lists[Common.ListAccountStatus];
                    foreach (SPListItem item in list.Items)
                    {
                        if (item["Title"].Equals(accountStatus))
                        {
                            return item.ID + ";#" + accountStatus;
                        }
                    }
                }
            }
            return string.Empty;
        }

        private static string GetStatus(string status)
        {
            if (string.IsNullOrEmpty(status))
                return Common.StatusLive;
            if (status.Equals("complete", StringComparison.CurrentCultureIgnoreCase) || status.Equals("completed", StringComparison.CurrentCultureIgnoreCase))
                return Common.StatusCompleted;
            if (status.Equals("amendments required", StringComparison.CurrentCultureIgnoreCase))
                return Common.StatusAmendmentRequired;
            if (status.Equals("ready for audit", StringComparison.CurrentCultureIgnoreCase) || status.Equals("ready to audit", StringComparison.CurrentCultureIgnoreCase))
                return Common.StatusReady;
            if (status.Equals("live", StringComparison.CurrentCultureIgnoreCase))
                return Common.StatusLive;
            return Common.StatusLive;
        }

        public static string ConvertBoolean(string input)
        {
            if (input.Equals("APPROVED"))
                return "1";
            return "0";
        }
        //return the last Id if no match found (the last one is support office.
        public static int GetOfficeIdByCode(string code)
        {
            code = MatchCode(code).ToString();
            int officeId = 0;
            using(SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using(SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {

                    SPList officeList = docsbox.Lists[Common.ListOffices];
                    foreach (SPListItem item in officeList.Items)
                    {
                        officeId = item.ID;
                        string officeCode = item[FinanceBranchCode].ToString();
                        
                        if(officeCode.Equals(code, StringComparison.CurrentCultureIgnoreCase))
                        {
                            return officeId;
                        }
                    }
                }
            }
            return officeId;
        }
        public static int MatchCode(string code)
        {
            int iCode;

            if(Int32.TryParse(code, out iCode))
            {
                if (iCode > 13 && iCode < 25)
                    return iCode;
                if(iCode > 24 && iCode < 36)
                {
                    switch (iCode)
                    {
                        case 25:
                            return 22;
                        case 26:
                            return 23;
                        case 27:
                            return 17;
                        case 28:
                            return 19;
                        case 29:
                            return 16;
                        case 30:
                            return 20;
                        case 31:
                            return 15;
                        case 33:
                            return 21;
                        case 34:
                            return 14;
                        case 35:
                            return 18;
                        default:
                            return 24;
                    }
                }
            }
            return 24;

        }

        public static string GetOfficeNameByCode(string code)
        {
            using (SPSite site = new SPSite(ConfigurationManager.AppSettings["siteUrl"]))
            {
                using (SPWeb docsbox = site.OpenWeb(ConfigurationManager.AppSettings["docsboxWeb"]))
                {

                    SPList officeList = docsbox.Lists[Common.ListOffices];
                    foreach (SPListItem item in officeList.Items)
                    {
                        string officeCode = item[FinanceBranchCode].ToString();

                        if (officeCode.Equals(code, StringComparison.CurrentCultureIgnoreCase))
                        {
                            return item["Title"].ToString();
                        }
                    }
                }
            }
            return string.Empty;
        }
    }

    public class CsvRow : List<string>
    {
        public string LineText { get; set; }
    }

    /// <summary>
    /// Class to write data to a CSV file
    /// </summary>
    public class CsvFileWriter : StreamWriter
    {
        public CsvFileWriter(Stream stream)
            : base(stream)
        {
        }

        public CsvFileWriter(string filename)
            : base(filename)
        {
        }

        /// <summary>
        /// Writes a single row to a CSV file.
        /// </summary>
        /// <param name="row">The row to be written</param>
        public void WriteRow(CsvRow row)
        {
            StringBuilder builder = new StringBuilder();
            bool firstColumn = true;
            foreach (string value in row)
            {
                // Add separator if this isn't the first value
                if (!firstColumn)
                    builder.Append(',');
                // Implement special handling for values that contain comma or quote
                // Enclose in quotes and double up any double quotes
                if (value.IndexOfAny(new char[] { '"', ',' }) != -1)
                    builder.AppendFormat("\"{0}\"", value.Replace("\"", "\"\""));
                else
                    builder.Append(value);
                firstColumn = false;
            }
            row.LineText = builder.ToString();
            WriteLine(row.LineText);
        }

    }

    public class DocsboxUpdater
    {
        public string DealId { get; set; }
        public DateTime RevisionDate { get; set; }
        public DateTime ContractDate { get; set; }
    }

}
